﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Reports.GoodsReport
{
    public class DataLoader
    {
        public static void GetInputData(Reports.GoodsReport.DataSet.DataInputDataTable data, string connectionString, DateTime dateF, DateTime dateT)
        {
            string QueryString1 =
@"SELECT     c.Name, d.DocDate, d.ContragentDocNumber as DocNumber,
(SELECT SUM(/*CD.InputLines.SupplierPrice*/CD.Parties.RetailPrice * CD.InputLines.DocQntty)
FROM CD.Parties INNER JOIN
CD.InputLines ON CD.Parties.InputLineId = CD.InputLines.Id
WHERE (CD.Parties.DocId = d.Id)) AS Sum, CAST(0 AS decimal) AS Tara
FROM CA.Documents AS d INNER JOIN
CA.Contragents AS c ON d.ContragentId = c.Id
WHERE     (d.DocTypeId = 2) AND d.DocDate BETWEEN @DateF AND @DateT";
            string QueryString =
@"SELECT C.ShortName as Name, D.DocDate, D.ContragentDocNumber as DocNumber, 
sum(L.RetailSum) as Sum,CAST(0 AS decimal) AS Tara
--, T.ShortName AS DocTypeName
FROM CA.GoodsDocLines AS L INNER JOIN CA.Documents AS D ON L.DocId = D.Id 
INNER JOIN CO.DocTypes AS T ON D.DocTypeId = T.Id 
LEFT OUTER JOIN CA.Contragents AS C ON D.ContragentId = C.Id
WHERE L.Qntty > 0 AND D.DocDate > @DateF AND D.DocDate <=@DateT
GROUP BY D.DocDate,D.ContragentDocNumber,C.ShortName
order by D.DocDate";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
        public static void GetOutputData(Reports.GoodsReport.DataSet.DataOutputDataTable data, string connectionString, DateTime dateF, DateTime dateT)
        {
            string QueryString1 =
@"with Data(DocDate, Price) as
(
SELECT convert(datetime, convert(varchar,DocDate,104),104),  Qntty * RetailSum
FROM dbo.vwGoodsFlow
WHERE (DocTypeId = 3) and DocDate BETWEEN @DateF AND @DateT
)
select 'Чеки' as Name, '' as DocNumber, DocDate,sum(Price) as [Sum], CAST(0 AS decimal) AS Tara
from Data
group by DocDate
order by DocDate";
            string QueryString =
@"SELECT T.ShortName AS Name, convert(datetime, convert(varchar,D.DocDate,104),104) as DocDate,'' as DocNumber, 
sum(L.RetailSum*-1) as [Sum],CAST(0 AS decimal) AS Tara
FROM CA.GoodsDocLines AS L INNER JOIN CA.Documents AS D ON L.DocId = D.Id 
INNER JOIN CO.DocTypes AS T ON D.DocTypeId = T.Id 
LEFT OUTER JOIN CA.Contragents AS C ON D.ContragentId = C.Id
WHERE L.Qntty < 0 AND D.DocDate > @DateF AND D.DocDate <= @DateT
GROUP BY convert(datetime, convert(varchar,D.DocDate,104),104), T.ShortName
order by 2";
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter DateF = new SqlParameter("@DateF", SqlDbType.DateTime);
                    DateF.Value = dateF;
                    command.Parameters.Add(DateF);
                    SqlParameter DateT = new SqlParameter("@DateT", SqlDbType.DateTime);
                    DateT.Value = dateT;
                    command.Parameters.Add(DateT);
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(data);
                }
            }
            catch (SqlException se)
            {
                System.Windows.Forms.MessageBox.Show(se.Message);
            }
            catch (Exception ee)
            {
                System.Windows.Forms.MessageBox.Show(ee.Message);
            }
        }
    }
}
